
/**
	Author		: Rohan	
	Date		: April 27th 2004
	Descrition	: This procedure will insert or update the customer information
			  pertaining to the GP customer in the Chiptrac DataBase
	Tables Updated	: Customer
	Usage		: 
	 
	Modified by	: Rajkumar
	Date		: 09 June 2004
	Modification	: Added Billing Address parameters to insert into customer_phone_fax_email table		
*/

ALTER PROC sp_ct_sls_save_gp_customer( 

@GP_DEX_ROW_ID              INT,
@CUSTOMER_NUMBER	    VARCHAR(50),
@CUSTOMER_NAME		    VARCHAR(50),
@CONTACT_PERSON		    VARCHAR(50),
@SHIPPING_METHOD	    VARCHAR(100),
@SHIPPING_ACCOUNT	    VARCHAR(100),
@REGION                     VARCHAR(50),
@REP_OFFICE                 VARCHAR(50),
@SALES_PERSON               VARCHAR(50),
@SHIPPING_INSTRUCTION       TEXT,
@SHIPPING_ADDRESS_ID        INT,
@BILLING_ADDRESS_ID         INT,
@PRIORITY                   VARCHAR(10),
@CANCELLATION_WINDOW        INT,
@REVISION_RESTRICTION       INT,
@LOCATION_RESTRICTION       INT,
@DATECODE_RESTRICTION       INT,
@NO_OF_DATECODE_RESTRICTION VARCHAR(50),
@TRAYCODE_RESTRICTION       INT,
@NO_OF_TRAYCODE_RESTRICTION VARCHAR(50),
@LEAST_SHIPPABLE_QTY        INT,
@CREDIT_LIMIT               INT,
@REP_ID                     VARCHAR(50),
@REP_NAME                   VARCHAR(50),
@NET_TERMS	            VARCHAR(50),
@SHIPPING_RESTRICTION       INT,
@NO_OF_SHIPPING_DAYS_NOT_EARLIER_THAN INT,
@CUSTOMER_PHONE             VARCHAR(50),
@CUSTOMER_FAX	            VARCHAR(50),
@CUSTOMER_EMAIL	            VARCHAR(50),
@SALES_REP                  VARCHAR(50),
@Print_Name		    VARCHAR(50),
@Supplier_Number            VARCHAR(50),
@Contract_Number            VARCHAR(50),
@Specification_Number       VARCHAR(50),
@Custom_Requirements        INT,
@ECN_Change_Notification    INT,
@JIT_WINDOW	            INT,
@shipping_instructions_id   INT,	
@CUSTOMER_ID                INT OUT)


AS
BEGIN
	DECLARE @row_count  INT

	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM GP_CT_CUSTOMER_MAPPING WHERE DEX_ROW_ID = @GP_DEX_ROW_ID
	IF( @@ROWCOUNT = 0 )
	BEGIN
		--INSERT THE GP CUSTOMER DETAILS INTO THE CUSTOMER TABLE
		INSERT INTO CUSTOMER( C_Name, C_Number, C_Contact_Person, C_Shipping_Method_1, C_Shipping_Method_2, C_Shipping_method_3, C_Act_Number_1, C_Act_Number_2, C_Act_Number_3, C_Region, C_Rep_Office, C_Sales_Person, C_Shipping_Instructions, C_Default_Shipping_Address_ID, C_Default_Billing_Address_ID, C_Priority, C_Cancellation_Window, C_Revision_Restriction, C_Location_Restriction, C_Datecode_Restriction, C_No_Of_Datecode_Restriction, C_Traycode_Restriction, C_No_Of_Traycode_Restriction, C_Least_Shippable_Qty, C_Credit_Limit, C_Representative_ID, C_Representative_Name, C_Net_Terms, C_Shipping_Restriction, C_NoOf_ShippingDays_NotEarlier_Than, C_Disabled, C_Sales_Rep, C_Print_Name, C_Supplier_Number, C_Contract_Number, C_Specification_Number, C_Custom_Requirements, C_ECN_Change_Notification, C_JIT_WINDOW, C_SHIPPING_INSTRUCTIONS_ID  )  
		             VALUES ( @CUSTOMER_NAME, @CUSTOMER_NUMBER, @CONTACT_PERSON, ISNULL( @SHIPPING_METHOD, '' ), '', '', ISNULL( @SHIPPING_ACCOUNT, '' ), '', '', @REGION, @REP_OFFICE, @SALES_PERSON, @SHIPPING_INSTRUCTION, @SHIPPING_ADDRESS_ID, @BILLING_ADDRESS_ID, @PRIORITY, @CANCELLATION_WINDOW, @REVISION_RESTRICTION, @LOCATION_RESTRICTION, @DATECODE_RESTRICTION, @NO_OF_DATECODE_RESTRICTION, @TRAYCODE_RESTRICTION, @NO_OF_TRAYCODE_RESTRICTION, @LEAST_SHIPPABLE_QTY, @CREDIT_LIMIT, @REP_ID, @REP_NAME, @NET_TERMS, @SHIPPING_RESTRICTION, @NO_OF_SHIPPING_DAYS_NOT_EARLIER_THAN, 1, @SALES_REP, @Print_Name, @Supplier_Number, @Contract_Number, @Specification_Number, @Custom_Requirements, @ECN_Change_Notification, @JIT_WINDOW, @shipping_instructions_id )

		SELECT @CUSTOMER_ID = @@IDENTITY

		INSERT INTO GP_CT_CUSTOMER_MAPPING( CUSTOMER_ID, DEX_ROW_ID ) VALUES( @CUSTOMER_ID, @GP_DEX_ROW_ID )
		IF ( @SHIPPING_METHOD IS NOT NULL AND LEN ( LTRIM( RTRIM ( @SHIPPING_METHOD ) ) ) > 0) 
		BEGIN
			INSERT INTO SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES( @CUSTOMER_ID, @SHIPPING_METHOD, ISNULL( @SHIPPING_ACCOUNT, '' ) )
		END
		
		--For Specifiying that this Customer is of Type "Customer". 1 means Customer.
		INSERT INTO CUSTOMER_TYPE_RELATIONSHIP VALUES ( @CUSTOMER_ID, 1 )
	END
	ELSE
	BEGIN
		--UPDATE THE GP CUSTOMER DETAILS INTO THE CUSTOMER TABLE

		
		--Bringing the Actual Shipping Method for the Given Shipping Mehtod And Account.
		SELECT @row_count = COUNT(*) FROM SHIPPING_METHODS_TABLE WHERE SM_CUSTOMER_ID = @CUSTOMER_ID AND SUBSTRING( RTRIM(SHIPPING_METHOD), 1, 13 ) like  SUBSTRING( @SHIPPING_METHOD, 1, 13 )+'%' And SHIPPING_ACCOUNT = @SHIPPING_ACCOUNT
		IF @row_count = 0
		BEGIN
			IF ( @SHIPPING_METHOD IS NOT NULL AND LEN ( LTRIM( RTRIM ( @SHIPPING_METHOD ) ) ) > 0) 
			BEGIN
				INSERT INTO SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES( @CUSTOMER_ID, @SHIPPING_METHOD, ISNULL( @SHIPPING_ACCOUNT, '' ) )
			END
		END
		ELSE
		BEGIN
			IF ( @SHIPPING_METHOD IS NOT NULL AND LEN ( LTRIM( RTRIM ( @SHIPPING_METHOD ) ) ) > 0) 
			BEGIN
				SELECT @SHIPPING_METHOD = SHIPPING_METHOD, @SHIPPING_ACCOUNT = SHIPPING_ACCOUNT FROM SHIPPING_METHODS_TABLE WHERE SM_CUSTOMER_ID = @CUSTOMER_ID AND SUBSTRING( RTRIM(SHIPPING_METHOD), 1, 13 ) like  SUBSTRING( @SHIPPING_METHOD, 1, 13 )+'%' And SHIPPING_ACCOUNT = @SHIPPING_ACCOUNT	
			END
		END


		UPDATE CUSTOMER SET C_Name                        = @CUSTOMER_NAME, 
			    	    C_Number                      = @CUSTOMER_NUMBER, 
		        	    C_Contact_Person              = @CONTACT_PERSON, 
				    C_Shipping_Method_1           = @SHIPPING_METHOD, 
			            C_Act_Number_1                = @SHIPPING_ACCOUNT, 
		                    C_Region                      = @REGION, 
				    C_Rep_Office                  = @REP_OFFICE, 
		    		    C_Sales_Person                = @SALES_PERSON, 
			            C_Shipping_Instructions       = @SHIPPING_INSTRUCTION, 
				    C_Default_Shipping_Address_ID = @SHIPPING_ADDRESS_ID, 
				    C_Default_Billing_Address_ID  = @BILLING_ADDRESS_ID, 
				    C_Priority                    = @PRIORITY, 
				    C_Cancellation_Window         = @CANCELLATION_WINDOW, 
				    C_Revision_Restriction        = @REVISION_RESTRICTION, 
				    C_Location_Restriction        = @LOCATION_RESTRICTION, 
			 	    C_Datecode_Restriction        = @DATECODE_RESTRICTION, 
				    C_No_Of_Datecode_Restriction  = @NO_OF_DATECODE_RESTRICTION, 
                               	    C_Traycode_Restriction        = @TRAYCODE_RESTRICTION, 
			            C_No_Of_Traycode_Restriction  = @NO_OF_TRAYCODE_RESTRICTION, 
       	                            C_Least_Shippable_Qty         = @LEAST_SHIPPABLE_QTY, 
               	                    C_Credit_Limit                = @CREDIT_LIMIT, 
				    C_Representative_ID           = @REP_ID,
				    C_Representative_Name         = @REP_NAME,
				    C_Net_Terms                   = @NET_TERMS,
				    C_Shipping_Restriction        = @SHIPPING_RESTRICTION,
				    C_Sales_Rep                   = @SALES_REP,
				    C_Print_Name		  = @Print_Name,
			  	    C_Supplier_Number		  = @Supplier_Number,
				    C_Contract_Number	  	  = @Contract_Number,
				    C_Specification_Number	  = @Specification_Number,
				    C_Custom_Requirements   	  = @Custom_Requirements,
				    C_ECN_Change_Notification     = @ECN_Change_Notification,
				    C_JIT_WINDOW  		  = @JIT_WINDOW,
       	                            C_NoOf_ShippingDays_NotEarlier_Than = @NO_OF_SHIPPING_DAYS_NOT_EARLIER_THAN,
				    C_SHIPPING_INSTRUCTIONS_ID    = @shipping_instructions_id  	 WHERE Customer_ID = @CUSTOMER_ID
	END
		
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER_PHONE_FAX_EMAIL WHERE CUSTOMER_ID = @CUSTOMER_ID
	IF( @@ROWCOUNT = 0 )
	BEGIN
		INSERT INTO CUSTOMER_PHONE_FAX_EMAIL ( Customer_ID, Phone_Number, Fax_Number, email )
		VALUES ( @CUSTOMER_ID, @CUSTOMER_PHONE, @CUSTOMER_FAX, @CUSTOMER_EMAIL )
	END
	ELSE
	BEGIN
		UPDATE CUSTOMER_PHONE_FAX_EMAIL 
		   SET Phone_Number 	=  @CUSTOMER_PHONE,
			Fax_Number 	=  @CUSTOMER_FAX,
			email 		=  @CUSTOMER_EMAIL
		  WHERE Customer_ID	=  @CUSTOMER_ID

	END	

END --END OF PROC
							